package com.raychi.demo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.raychi.demo.entity.Mark;



public class MarkDao {
	/**
	 * 查找指定id的学生姓名、学科和成绩 用做链接的方法
	 * @param conn 连接数据库
	 * @param id 页面传入的学生id
	 * @return 学生姓名、学科和成绩的列表
	 * @throws Exception
	 */
	public List<Mark> getMarks(Connection conn, int id) throws Exception{
		List<Mark> marks = new ArrayList<Mark>();
		Statement st = conn.createStatement();
		String sql = "select m.*, s.name from Mark m left join Student s on m.userId=s.id where s.id=" + id;
		ResultSet rs = st.executeQuery(sql);
		Mark mark = null;
		while(rs.next()){
			mark = creatMark(rs);
			marks.add(mark);
		}
		return marks;
	}
	
	/**
	 * 用左连接的方法 获取学科 成绩 和学生姓名
	 * @param conn 连接数据库
	 * @return 学生姓名、学科和成绩的列表
	 * @throws Exception
	 */
	public List<Mark> listMarks(Connection conn) throws Exception{
		List<Mark> marks = new ArrayList<Mark>();
		Statement st = conn.createStatement();
		String sql = "select m.*, s.name from Mark m left join Student s on m.userId=s.id";
		ResultSet rs = st.executeQuery(sql);
		Mark mark = null;
		while(rs.next()){
			mark = creatMark(rs);
			marks.add(mark);
		}
		return marks;
	}
	
	/**
	 * 通过页面传入的id 将指定的信息删除
	 * @param mark 页面传入的id
	 * @param conn 连接数据库
	 * @throws Exception
	 */
	public void delete(Mark mark, Connection conn) throws Exception{
		String sql = "delete from Mark where id=" + mark.getId();
		Statement st = conn.createStatement();
		st.execute(sql);
	}
	
	/**
	 * 通过页面传入的id 将指定的信息进行更改
	 * @param mark 页面传入的id，学科 和分数
	 * @param conn
	 * @throws Exception
	 */
	public void update(Mark mark, Connection conn) throws Exception{
		String sql = "update Mark set subject = ?,score = ? where id =?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, mark.getSubject());
		ps.setInt(2, mark.getScore());
		ps.setInt(3, mark.getId());
		ps.executeUpdate();
	}
	
	/**
	 * 将页面输入的信息存入数据库
	 * @param mark 页面输入的学科、成绩 和学号
	 * @param conn 连接数据库
	 * @throws Exception
	 */
	public void save(Mark mark, Connection conn) throws Exception{
		String sql = "insert into Mark (subject, score, userId) values (?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, mark.getSubject());
		ps.setInt(2, mark.getScore());
		ps.setInt(3, mark.getUserId());
		ps.execute();
	}
	
	/**
	 * 通过页面传入的id 查找对应的信息
	 * @param conn 
	 * @param id 页面传入的id
	 * @return 对应信息的列表
	 * @throws Exception
	 */
	public Mark getMarkById(Connection conn, int id) throws Exception{
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery("select * from Mark where id=" + id);
		Mark mark = null;
		while(rs.next()){
			mark = getMarkById(rs);
		}
		return mark;
	}
	
	/**
	 * 创建一个mark，其中包含了来自学生数据库的name
	 * @param rs 传入的mark的所有信息
	 * @return mark的列表
	 * @throws Exception
	 */
	public	static Mark creatMark(ResultSet rs) throws Exception{
		Mark mark = new Mark();
		mark.setId(rs.getInt("id"));
		mark.setScore(rs.getInt("score"));
		mark.setSubject(rs.getString("subject"));
		mark.setUserId(rs.getInt("userId"));
		mark.setName(rs.getString("name"));
		return mark;
	}
	
	/**
	 * 创建一个mark 
	 * @param rs 传入的mark的所有信息	
	 * @return mark的表单
	 * @throws SQLException
	 */
	public static Mark getMarkById(ResultSet rs) throws SQLException{
		Mark mark = new Mark();
		mark.setId(rs.getInt("id"));
		mark.setScore(rs.getInt("score"));
		mark.setSubject(rs.getString("subject"));
		mark.setUserId(rs.getInt("userId"));
		return mark;
	}
}
